package com.dingding.manager.modules.system.sql;

/**
 * 
* @Description: 省市联查公共方法SQL
* @author haoxiaojun 
* @date 2016年12月7日 下午3:01:09
* @since V 2.5.7
 */
public class ProAndCitySQL {
	
	/**
	 * 根据省份Id查询出当前省份下的所有城市
	 * SELECT a.sysCityId, a.cityName FROM dd_sys_city a LEFT JOIN dd_sys_provice_city b ON a.sysCityId = b.sysCityId LEFT JOIN dd_sys_province c ON b.proviceId = c.provice_id WHERE 1 = 1 AND c.provice_id = 1
	 */
	public static final String GET_CITY_BY_ID = "SELECT a.sysCityId, a.cityName FROM dd_sys_city a LEFT JOIN dd_sys_provice_city b ON a.sysCityId = b.sysCityId LEFT JOIN dd_sys_province c ON b.proviceId = c.provice_id WHERE 1 = 1 ";
	
	/**
	 * 根据当前登录用户查看可以操作的省份信息
	 */
	public static final String GET_PRO_BY_USER_ID = "SELECT p.provice_id, p.provice_name FROM dd_sys_user u LEFT JOIN dd_organization o ON u.companyId = o.organizationId LEFT JOIN dd_sys_city c ON c.sysCityId = o.cityId LEFT JOIN dd_sys_provice_city pc ON c.sysCityId = pc.sysCityId LEFT JOIN dd_sys_province p ON pc.proviceId = p.provice_id WHERE 1 = 1 AND u.sysUserId = ? ";
	
	/**
	 * 根据公司Id查看省份和城市信息
	 */
	public static final String GET_PRO_CITY_BY_COMPANY_ID = "SELECT p.provice_id AS provinceId, p.provice_name AS provinceName, c.sysCityId AS cityId, c.cityName AS cityName FROM dd_sys_user u   LEFT JOIN dd_organization o     ON u.companyId = o.organizationId   LEFT JOIN dd_sys_city c     ON c.sysCityId = o.cityId   LEFT JOIN dd_sys_provice_city pc     ON c.sysCityId = pc.sysCityId   LEFT JOIN dd_sys_province p     ON pc.proviceId = p.provice_id WHERE 1 = 1 AND u.companyId = ? ";
}
